if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].msp_CariKartGuncelleXML') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].msp_CariKartGuncelleXML
GO
 
/* BU PROCEDURE İÇİNDE DEĞİŞİKLİK YAPILIRSA GLBSP İÇİNDEKİ msp_CariKartGuncelleXML İÇİNDE DE YAPILMALIDIR.*/
 
CREATE PROCEDURE [dbo].msp_CariKartGuncelleXML(@outtablename nvarchar(500),@xmlfilename nvarchar(1000))
AS
BEGIN
  DECLARE @sqlstr NVARCHAR(max)
 
  IF object_id('tempdb..#XmlTable') is not null DROP TABLE #XmlTable
  CREATE TABLE #XmlTable(XmlData XML)
  SET @sqlstr = 'INSERT INTO #XmlTable(XmlData) SELECT * FROM OPENROWSET(BULK '''+@xmlfilename+''', SINGLE_BLOB) As X'
  execute (@sqlstr)
  IF object_id('tempdb..#tempcaritable') is not null DROP TABLE #tempcaritable
 
  SELECT href.value('(Identifier/text())[1]', 'nvarchar(25)') as eiu_Identifier,
         href.value('(Alias/text())[1]', 'varchar(120)') as eiu_Alias,
         href.value('(Title/text())[1]', 'varchar(300)') as eiu_Title,
         href.value('(Type/text())[1]', 'varchar(50)') as eiu_Type,
         href.value('(FirstCreationTime/text())[1]', 'datetime') as eiu_FirstCreationTime,
         href.value('(AliasCreationTime/text())[1]', 'datetime') as eiu_AliasCreationTime,
         href.value('(AliasType/text())[1]', 'int') as eiu_AliasType
  into #tempcaritable
  FROM #XmlTable CROSS APPLY XmlData.nodes('UserList/User') AS userinfo(href)
 
  if not exists (select * from dbo.sysobjects where id = object_id(@outtablename) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  begin
    SET @sqlstr = ' CREATE TABLE '+
                  '  dbo.'+@outtablename+'( '+
                  '    eiu_Identifier        nvarchar(25)  NOT NULL, '+
                  '    eiu_Alias             nvarchar(120) NOT NULL, '+
                  '    eiu_Title             nvarchar(300) NULL, '+
                  '    eiu_Type              nvarchar(50)  NOT NULL, '+
                  '    eiu_FirstCreationTime datetime      NOT NULL, '+
                  '    eiu_AliasCreationTime datetime      NULL,  '+
                  '    eiu_AliasType         int           NOT NULL  '+
                  ') '
    execute (@sqlstr)
    SET @sqlstr = 'CREATE INDEX NDX_'+@outtablename+'_00 ON dbo.'+@outtablename+' (eiu_Identifier, eiu_Alias, eiu_AliasType) '
    execute (@sqlstr)
    SET @sqlstr = 'CREATE INDEX NDX_'+@outtablename+'_01 ON dbo.'+@outtablename+' (eiu_Identifier) '
    execute (@sqlstr)
    SET @sqlstr = 'CREATE INDEX NDX_'+@outtablename+'_02 ON dbo.'+@outtablename+' (eiu_Alias) '
    execute (@sqlstr)
    SET @sqlstr = 'CREATE INDEX NDX_'+@outtablename+'_03 ON dbo.'+@outtablename+' (eiu_Title) '
    execute (@sqlstr)
  end
  else
  begin
    SET @sqlstr = ' TRUNCATE TABLE '+@outtablename
    execute (@sqlstr)
  end
 
  SET @sqlstr = 'INSERT INTO dbo.' + @outtablename + ' select * from #tempcaritable'
  execute (@sqlstr)
END
GO